const connection = require('../../database/connection')

module.exports = {
    // 订单管理
    async getRecordsTotal(data) {
        const [[total]] = await connection.execute('SELECT COUNT(*) total FROM `records` r, `centers` c, `departments` d, `salesmans` s, `promotion_channels` pc, `distributions` db WHERE CONCAT( r.`name`, r.`phone`, c.`name`, d.`name`, s.`name` ) LIKE "%' + data.search + '%" ' + data.sql + '' + data.time + ' AND c.id = r.center_id AND d.id = r.department_id AND s.id = r.salesman_id AND pc.id = r.promotion_id AND db.id = r.distribution_id AND r.`deleted_at` IS NULL AND c.`deleted_at` IS NULL AND d.`deleted_at` IS NULL AND s.`deleted_at` IS NULL AND pc.`deleted_at` IS NULL AND db.`deleted_at` IS NULL;')
        return total
    },
    async getRecords(data) {
        const [records] = await connection.execute('SELECT r.`id`, r.`time`, r.`name`, r.`deposit_to_achievement`, r.`phone`, r.`negotiator_id`, r.`is_sign`, r.`deposit`, r.`created_at`, r.`notsigning_id` notsign, r.`is_mortgage`, pc.`name` promotion_name, db.`name` distribution_name, c.`name` center_name, d.`name` dept_name, s.`name` salesman_name FROM `records` r, `centers` c, `departments` d, `salesmans` s, `promotion_channels` pc, `distributions` db WHERE CONCAT( r.`name`, r.`phone`, c.`name`, d.`name`, s.`name` ) LIKE "%' + data.search + '%" ' + data.sql + '' + data.time + ' AND c.id = r.center_id AND d.id = r.department_id AND s.id = r.salesman_id AND pc.id = r.promotion_id AND db.id = r.distribution_id AND r.`deleted_at` IS NULL AND c.`deleted_at` IS NULL AND d.`deleted_at` IS NULL AND s.`deleted_at` IS NULL AND pc.`deleted_at` IS NULL AND db.`deleted_at` IS NULL ORDER BY r.id LIMIT ' + data.limit + ' OFFSET ' + data.offset + ';')
        return records
    },
    async addRecord(data) {
        const [record] = await connection.execute('INSERT INTO `records` (`promotion_id`, `deposit_to_achievement`, `charge`, `time`, `name`, `distribution_id`, `phone`, `deposit`, `is_mortgage`, `notsigning_id`, `is_sign`, `center_id`, `department_id`, `salesman_id`, `negotiator_id`, `created_at`) VALUES ("' + data.promotion_id + '", "' + data.deposit_to_achievement + '", "' + data.charge + '", "' + data.time + '", "' + data.name + '", "' + data.distribution_id + '", "' + data.phone + '", "' + data.deposit + '", "' + data.is_mortgage + '", "' + data.notsign_id + '", "' + data.is_sign + '", "' + data.center_id + '", "' + data.dept_id + '", "' + data.salesman_id + '", "' + data.negotiator + '", NOW());')
        return record
    },
    async getRecordById(id) {
        const [[record]] = await connection.execute('SELECT r.* FROM `records` r WHERE r.`id` = "' + id + '" AND r.`deleted_at` IS NULL;')
        return record
    },
    async getRecordByRelationId(data) {
        const [record] = await connection.execute('SELECT r.* FROM `records` r WHERE r.`' + data.type + '` = "' + data.id + '" AND r.`deleted_at` IS NULL;')
        return record
    },
    async updateRelationS(data) {
        const [record] = await connection.execute('UPDATE `records` r SET r.`center_id` = "' + data.center_id + '", r.`department_id` = "' + data.dept_id + '" WHERE r.`salesman_id` = "' + data.id + '";')
        return record
    },
    async updateRelationD(data) {
        const [record] = await connection.execute('UPDATE `records` r SET r.`center_id` = "' + data.center_id + '" WHERE r.`department_id` = "' + data.id + '";')
        return record
    },
    async getRecordByPhone(data) {
        const [[record]] = await connection.execute('SELECT r.* FROM `records` r WHERE r.`phone` = "' + data + '" AND r.`deleted_at` IS NULL;')
        return record
    },
    async rmRecordById(id) {
        const [record] = await connection.execute('UPDATE `records` r SET r.`deleted_at` = NOW() WHERE r.`id` = "' + id + '";')
        return record
    },
    async updateRecord(data) {
        const [record] = await connection.execute('UPDATE `records` SET `time` = "' + data.time + '", `name` = "' + data.name + '", `distribution_id` = "' + data.distribution_id + '", `negotiator_id` = "' + data.negotiator + '", `deposit_to_achievement` = "' + data.deposit_to_achievement + '", `charge` = "' + data.charge + '", `promotion_id` = "' + data.promotion_id + '", `phone` = "' + data.phone + '", `deposit` = "' + data.deposit + '", `is_mortgage` = "' + data.is_mortgage + '", `notsigning_id` = "' + data.notsign_id + '", `is_sign` = "' + data.is_sign + '", `center_id` = "' + data.center_id + '", `department_id` = "' + data.dept_id + '", `salesman_id` = "' + data.salesman_id + '", `updated_at` = NOW() WHERE `id` = "' + data.id + '";')
        return record
    },

    // 计划管理
    async getPlansTotal(data) {
        const [[total]] = await connection.execute('SELECT COUNT(*) total FROM `plans` p, `centers` c, `departments` d WHERE p.`time` LIKE "' + data + '%" AND c.id = p.center_id AND d.id = p.department_id AND p.`deleted_at` IS NULL AND c.`deleted_at` IS NULL AND d.`deleted_at` IS NULL;')
        return total
    },
    async getPlans(data) {
        const [plans] = await connection.execute('SELECT p.`id`, p.`time`, p.`visit_plan`, p.`sign_plan`, p.`created_at`, c.`name` center_name, d.`name` dept_name FROM `plans` p, `centers` c, `departments` d WHERE p.`time` LIKE "' + data.search + '%" AND c.id = p.center_id AND d.id = p.department_id AND p.`deleted_at` IS NULL AND c.`deleted_at` IS NULL AND d.`deleted_at` IS NULL ORDER BY p.id LIMIT ' + data.limit + ' OFFSET ' + data.offset + ';')
        return plans
    },
    async addPlan(data) {
        const [plan] = await connection.execute('INSERT INTO `plans` (`time`, `visit_plan`, `sign_plan`, `center_id`, `department_id`, `created_at`) VALUES ("' + data.time + '", "' + data.visit_plan + '", "' + data.sign_plan + '", "' + data.center_id + '", "' + data.dept_id + '", NOW());')
        return plan
    },
    async getPlanById(id) {
        const [[plan]] = await connection.execute('SELECT p.* FROM `plans` p WHERE p.`id` = "' + id + '" AND p.`deleted_at` IS NULL;')
        return plan
    },
    async getPlanByTime(data) {
        const [[plan]] = await connection.execute('SELECT p.*, c.`name` center_name, d.`name` dept_name FROM `plans` p, `centers` c, `departments` d WHERE p.`time` = "' + data.time + '" AND c.`name` = "' + data.center + '" AND p.`center_id` = c.`id` AND p.`department_id` = d.`id` AND d.`name` = "' + data.department + '" AND p.`deleted_at` IS NULL AND c.`deleted_at` IS NULL AND d.`deleted_at` IS NULL;')
        return plan
    },
    async rmPlanById(id) {
        const [plan] = await connection.execute('UPDATE `plans` p SET p.`deleted_at` = NOW() WHERE p.`id` = "' + id + '";')
        return plan
    },
};

